package com.arong.swing.db;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.droid.java.beans.BeanInfo;
import org.droid.java.beans.IntrospectionException;
import org.droid.java.beans.Introspector;
import org.droid.java.beans.PropertyDescriptor;

import com.arong.swing.db.entity.PagingInfo;
import com.arong.swing.db.entity.PagingResult;

/**
 * <code>JDBCUtils</code>继承自<code>DbUtils</code>，并对方法进行了重载，以
 * JDBC的方式实现数据库操作工具类。本组件使用Apache的commons-dbutils实现。
 * 
 * @see com.anrong.core.database.DbUtils
 */
public final class JDBCUtils {
	
	 private BasicRowProcessor rowProcessor = new BasicRowProcessor(new EntityBeanProcessor());
	 
	 private String dbFilePath;
	 public JDBCUtils(String dbFilePath){
		 this.dbFilePath= dbFilePath;
	 }
	/**
	 * 查询数据库内容，并返回由数据表实体对象组成的列表，数据表实体对象均为 <code>Entity</code>类的子类。
	 * 
	 * @see com.anrong.core.entity.Entity
	 * @param sql
	 *            数据库查询语句
	 * @param clazz
	 *            数据表实体对象的类
	 * @param params
	 *            查询语句中的相应参数
	 * @param <T>
	 *            数据表实体对象
	 * @return 数据表实体对象组成的列表，如果查询结果为空，则应返回长度为0的列表
	 * @throws SQLException
	 *             数据库操作异常
	 */
	public <T> List<T> queryList(String sql, Class<T> clazz, Object... params)
			throws SQLException {
		List<T> rsList = null;
		Connection conn = null;
		try{
			conn = getConnection();
			QueryRunner runner = new QueryRunner();
	
			ResultSetHandler<List<T>> handler = new BeanListHandler<T>(clazz, rowProcessor);
			rsList = runner.query(conn, sql, handler, params);
			return rsList == null ? new ArrayList<T>(0) : rsList;
		}catch (SQLException e){
			throw e;
		}finally{
			closeConn(conn);
		}
	}

	/**
	 * 查询数据库内容，并返回由数据表实体对象（如果查询结果不止一条记录，则返回第 一条记录组成的数据库表实体对象）。数据表实体对象均为<code>Entity</code>类的子类。
	 * 
	 * @see com.anrong.core.entity.Entity
	 * @param sql
	 *            数据库查询语句
	 * @param clazz
	 *            数据表实体对象的类
	 * @param params
	 *            查询语句中的相应参数
	 * @param <T>
	 *            数据表实体对象
	 * @return 数据表实体对象（如果查询结果为空，则返回null）
	 * @throws SQLException
	 *             数据库操作异常
	 */
	public <T> T queryBean(String sql, Class<T> clazz, Object... params) throws SQLException {
		T bean = null;
		Connection conn = null;
		try{
			conn = getConnection();
			QueryRunner runner = new QueryRunner();
	
			ResultSetHandler<T> handler = new BeanHandler<T>(clazz, rowProcessor);
			bean = runner.query(conn, sql, handler, params);
			return bean;
		}catch (SQLException e){
			throw e;
		}finally{
			closeConn(conn);
		}
	}
	protected static String buildMaxRowSql(String sql) {
        StringBuilder sqlFinal = new StringBuilder();

        sqlFinal.append("select count(*) num from (");
        sqlFinal.append(sql);
        sqlFinal.append(")");

        return sqlFinal.toString();
    }
    protected static String buildPagingSql(String sql, PagingInfo pagInfo) {
	        int pageSize = pagInfo.getPageRows(); // 每页显示记录数
	        int pageNo = pagInfo.getPageNum(); // 第几页
	        if(pageNo < 0) {
	            pageNo = 1;
	        }
	        StringBuilder sqlFinal = new StringBuilder();
	        sqlFinal.append("select * from (");
	        sqlFinal.append(sql);
	        sqlFinal.append(") limit  " +pageSize + " offset "
	                + new Integer((pageNo - 1) * pageSize));
	        return sqlFinal.toString();
	}
	public <T> PagingResult<T> queryList(String sql, Class<T> clazz,PagingInfo pagingInfo, Object... params) throws SQLException {
		PagingResult<T> result = new PagingResult<T>();
		List<T> rsList = null;

		Connection conn = null;
		try{
			conn = getConnection();
			QueryRunner runner = new QueryRunner();

			String maxRowSql = buildMaxRowSql(sql);
			long maxRow = queryNumValue(conn,maxRowSql, params);

			String pagingSql = buildPagingSql(sql, pagingInfo);
			ResultSetHandler<List<T>> handler = new BeanListHandler<T>(clazz, rowProcessor);
			rsList = runner.query(conn, pagingSql, handler, params);

			pagingInfo.setTotalRows(maxRow);
			result.setPagingInfo(pagingInfo);
			result.setResultList(rsList == null ? new ArrayList<T>(0) : rsList);

			return result;
		}catch (SQLException e){
			throw e;
		}finally{
			closeConn(conn);
		}
	}
	
	private long queryNumValue(Connection conn,String sql, Object... params) throws SQLException {
		QueryRunner runner = new QueryRunner();
		return ((Integer) runner.query(conn, sql, new ScalarHandler(1), params)).longValue();
	}
	public long queryNumValue(String sql, Object... params) throws SQLException {
		Connection conn = null;
		try{
			conn = getConnection();
			return queryNumValue(conn,sql,params);
		}catch (SQLException e){
			throw e;
		}finally{
			closeConn(conn);
		}
	}
	
	/**
	 * 提供数据库执行操作，用于INSERT、DELETE、UPDATE的操作
	 * 
	 * @param sql
	 *            需要执行的SQL语句
	 * @param params
	 *            执行语句中的相应参数
	 * @return 数据库操作结果（标准JDBC的返回值）
	 * @throws SQLException
	 *             数据库操作异常
	 */
	private int execute(Connection conn,String sql, Object... params) throws SQLException {
		int result = 0;
		QueryRunner runner = new QueryRunner();

		result = runner.update(conn, sql, params);
		return result;
	}
	public int execute(String sql, Object... params) throws SQLException {
		int result = 0;
		Connection conn = null;
		try{
			conn = getConnection();
			
			Object returnObject = execute(conn,sql,params);
			result = (Integer) returnObject;
		} catch (SQLException e) {
			throw e;
		}finally{
			closeConn(conn);
		}

		return result;
	}
	private static PropertyDescriptor[] propertyDescriptors(Class<?> beanClass){
    	BeanInfo beanInfo = null;
		try {
			beanInfo = Introspector.getBeanInfo(beanClass);
		} catch (IntrospectionException e) {
			e.printStackTrace();
		}
		PropertyDescriptor[] props = beanInfo.getPropertyDescriptors();
		for(int i = 0; i < props.length; i++) {
            if(StringUtils.equals(props[i].getName(), "class")) {
                props = (PropertyDescriptor[])ArrayUtils.remove(props, i);
                break;
            }
        }
		return props;
    }
	private static <T> String buildInsertSql(Class<T> clazz, PropertyDescriptor[] props) throws SQLException {
        StringBuilder sqlFinal = new StringBuilder("insert into ");
        String[] tableName = StringUtils.splitByCharacterTypeCamelCase(clazz.getSimpleName());
        for(String t : tableName) {
            sqlFinal.append(StringUtils.upperCase(t) + "_");
        }
        sqlFinal.deleteCharAt(sqlFinal.length() - 1);
        sqlFinal.append(" (");

        StringBuilder columnSql = new StringBuilder();
        StringBuilder valueSql = new StringBuilder();
        String[] columnName;
        for(PropertyDescriptor p : props) {
            columnName = StringUtils.splitByCharacterTypeCamelCase(p.getName());
            for(String c : columnName) {
                columnSql.append(StringUtils.upperCase(c) + "_");
            }
            columnSql.deleteCharAt(columnSql.length() - 1);
            columnSql.append(",");
            valueSql.append("?,");
        }
        columnSql.deleteCharAt(columnSql.length() - 1);
        valueSql.deleteCharAt(valueSql.length() - 1);

        sqlFinal.append(columnSql).append(") values (").append(valueSql).append(")");

        return sqlFinal.toString();
    }
	/**
	 * 将实体Bean插入到数据库中，对实体Bean的属性与数据库字段的映射关系 有严格要求
	 * 
	 * @param entity
	 *            实体Bean
	 * @param <T>
	 *            实体Bean的类型
	 * @return 数据库操作结果（标准JDBC的返回值）
	 * @throws SQLException
	 *             数据库操作异常
	 */
	public <T> int insert(T entity) throws SQLException {
		int result = 0;
		PropertyDescriptor[] props = propertyDescriptors(entity.getClass());
		List<PropertyDescriptor> propsNotNull = new ArrayList<PropertyDescriptor>();
		List<Object> params = new ArrayList<Object>();
		Connection conn = null;
		try{
			conn = getConnection();
			Method getter;
			for (PropertyDescriptor  prop : props) {
				getter = prop.getReadMethod();
				Object columnValue = getter.invoke(entity);
				if(columnValue != null){
					propsNotNull.add(prop);
					params.add(columnValue);
				}
			}
			String insertSql = buildInsertSql(entity.getClass(), propsNotNull.toArray(new PropertyDescriptor[0]));
			Object returnObject = execute(conn,insertSql,  params.toArray());
			result = (Integer) returnObject;
		} catch (IllegalAccessException e) {
			throw new SQLException("查找JDBCUtils.execute方法时发生异常: " + e.getMessage());
		} catch (InvocationTargetException e) {
			throw new SQLException("调用JDBCUtils.execute方法时发生异常: "
					+ e.getTargetException().getMessage());
		}finally{
			closeConn(conn);
		}

		return result;
	}
	public <T> int insert(Connection conn,T entity) throws SQLException, Exception {
		PropertyDescriptor[] props = propertyDescriptors(entity.getClass());
		List<PropertyDescriptor> propsNotNull = new ArrayList<PropertyDescriptor>();
		List<Object> params = new ArrayList<Object>();
		Method getter;
		for (PropertyDescriptor  prop : props) {
			getter = prop.getReadMethod();
			Object columnValue = getter.invoke(entity);
			if(columnValue != null){
				propsNotNull.add(prop);
				params.add(columnValue);
			}
		}
		String insertSql = buildInsertSql(entity.getClass(), propsNotNull.toArray(new PropertyDescriptor[0]));
		Object returnObject = execute(conn,insertSql,  params.toArray());
		return (Integer) returnObject;
	}
	protected static <T> String buildUpdateSql(Class<T> clazz, PropertyDescriptor[] props, String... keyPropName) throws SQLException {
        StringBuilder sqlFinal = new StringBuilder("update ");
        String[] tableName = StringUtils.splitByCharacterTypeCamelCase(clazz.getSimpleName());
        for(String t : tableName) {
            sqlFinal.append(StringUtils.upperCase(t) + "_");
        }
        sqlFinal.deleteCharAt(sqlFinal.length() - 1);
        sqlFinal.append(" set ");

        StringBuilder columnSql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        String[] columnName;
        for(PropertyDescriptor p : props) {
            columnName = StringUtils.splitByCharacterTypeCamelCase(p.getName());
            for(String c : columnName) {
            	 columnSql.append(StringUtils.upperCase(c) + "_");
            }
            columnSql.deleteCharAt(columnSql.length() - 1);
            columnSql.append("=?,");
        }
        columnSql.deleteCharAt(columnSql.length() - 1);

        for(String key : keyPropName) {
        	columnName = StringUtils.splitByCharacterTypeCamelCase(key);
            for(String c : columnName) {
                whereSql.append(StringUtils.upperCase(c) + "_");
            }
            whereSql.deleteCharAt(whereSql.length() - 1);
            whereSql.append("=? and ");
        }
        
        sqlFinal.append(columnSql).append(" where ").append(StringUtils.removeEnd(whereSql.toString(), " and "));

        return sqlFinal.toString();
    }
	/**
	 * 将实体Bean更新到数据库中，对实体Bean的属性值与数据库字段的映射关系 有严格要求
	 * 
	 * @param entity
	 *            实体Bean
	 * @param keyPropName
	 *            主键属性名称
	 * @param <T>
	 *            实体Bean的类型
	 * @return 数据库操作结果（标准JDBC的返回值）
	 * @throws SQLException
	 *             数据库操作异常
	 */
	public <T> int update(T entity, String... keyPropName) throws SQLException {
		int result = 0;
		PropertyDescriptor[] props = propertyDescriptors(entity.getClass());
		Connection conn = null;
		try{
			conn = getConnection();
			List<PropertyDescriptor> propsNotNull = new ArrayList<PropertyDescriptor>();
			List<Object> params = new ArrayList<Object>();
			List<Object> paramKeys = new ArrayList<Object>();
			Method getter;
			boolean isKey = false;
			for (PropertyDescriptor  prop : props) {
				isKey = false;
				for (String key : keyPropName) {
					if (StringUtils.equalsIgnoreCase(prop.getName(), key)) {
						isKey = true;
						break;
					}
				}
				getter = prop.getReadMethod();
				if (isKey) {
					paramKeys.add(getter.invoke(entity));
				} else {
					Object columnValue = getter.invoke(entity);
					if(columnValue != null){
						propsNotNull.add(prop);
						params.add(columnValue);
					}
				}
			}
			params.addAll(paramKeys);
			String updateSql = buildUpdateSql(entity.getClass(), propsNotNull.toArray(new PropertyDescriptor[0]), keyPropName);
			Object returnObject = execute(conn,updateSql, params.toArray());
			result = (Integer) returnObject;
		} catch (IllegalAccessException e) {
			throw new SQLException("查找JDBCUtils.execute方法时发生异常: " + e.getMessage());
		} catch (InvocationTargetException e) {
			throw new SQLException("调用JDBCUtils.execute方法时发生异常: "
					+ e.getTargetException().getMessage());
		}finally{
			closeConn(conn);
		}

		return result;
	}
	public <T> int update(Connection conn,T entity, String... keyPropName) throws SQLException, Exception {
		PropertyDescriptor[] props = propertyDescriptors(entity.getClass());
		conn = getConnection();
		List<PropertyDescriptor> propsNotNull = new ArrayList<PropertyDescriptor>();
		List<Object> params = new ArrayList<Object>();
		List<Object> paramKeys = new ArrayList<Object>();
		Method getter;
		boolean isKey = false;
		for (PropertyDescriptor  prop : props) {
			isKey = false;
			for (String key : keyPropName) {
				if (StringUtils.equalsIgnoreCase(prop.getName(), key)) {
					isKey = true;
					break;
				}
			}
			getter = prop.getReadMethod();
			if (isKey) {
				paramKeys.add(getter.invoke(entity));
			} else {
				Object columnValue = getter.invoke(entity);
				if(columnValue != null){
					propsNotNull.add(prop);
					params.add(columnValue);
				}
			}
		}
		params.addAll(paramKeys);
		String updateSql = buildUpdateSql(entity.getClass(), propsNotNull.toArray(new PropertyDescriptor[0]), keyPropName);
		Object returnObject = execute(conn,updateSql, params.toArray());
		return (Integer) returnObject;
	}
	public Object[] setQryList(List qryList) {
		Object[] obj = null;
		if (qryList != null && qryList.size() > 0) {
			obj = new Object[qryList.size()];
			for (int i = 0; i < qryList.size(); i++) {
				obj[i] = qryList.get(i);
			}
		}
		return obj;
	}
	
	/**
	 * 通过Spring的DataSourceUtils.getConnection方法获取数据库连接对象，从而
	 * 实现将事务控制交给Spring的DataSourceTransactionManager来管理
	 * 
	 * @return 数据库连接对象
	 * @throws SQLException
	 *             数据库操作异常
	 */
	public Connection getConnection() throws SQLException {
		 try {
			DriverManager.registerDriver((Driver)(Class.forName("org.sqldroid.SQLDroidDriver", true, getClass().getClassLoader()).newInstance()));
		} catch (Exception e) {
			e.printStackTrace();
		}
		
        //建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
        //String path = JDBCUtils.class.getResource("/").getPath();
        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
        return conn;
	}
	
	public void closeConn(Connection conn) throws SQLException{
		if(conn != null){
			conn.close();
		}
	}
	
	
	public static final int CURRENT_PAGE_SHOW_DEFAULT = 1;
	public static final int PER_PAGE_SHOW_DEFAULT = 10;
	public static PagingInfo getPageInfo(String strCurrentPage,String strPageSize) {
		PagingInfo pageInfo = new PagingInfo();
		Integer currentPage = CURRENT_PAGE_SHOW_DEFAULT; // 初始当前页数
		Integer pageSize = PER_PAGE_SHOW_DEFAULT; // 初始每页显示数据数
		if (strCurrentPage != null) {
			currentPage = Integer.parseInt(strCurrentPage);
		}
		if (strPageSize != null) {
			pageSize = Integer.parseInt(strPageSize);
		}

		pageInfo.setPageNum(currentPage);
		pageInfo.setPageRows(pageSize);

		return pageInfo;
	}
	public static void main(String[] args) {
		
	}
}